package com.ruoyi.lhky.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ruoyi.lhky.pojo.Research;
import com.ruoyi.lhky.pojo.Tester;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface ResearchMapper extends BaseMapper<Research> {

    @Select("SELECT r.*, h.name AS hospitalName, u.workplace_id, ru.user_id " +
            "FROM research r " +
            "JOIN research_user ru ON r.id = ru.research_id " +
            "JOIN user u ON ru.user_id = u.id " +
            "JOIN hospital h ON u.workplace_id = h.id " +
            "WHERE ru.user_id = #{userId} OR r.hospital_id = #{workplaceId}")
    List<Research> searchByUserAndWorkplace(@Param("userId") Long userId, @Param("workplaceId") Long workplaceId);

    @Select("SELECT r.*, h.name AS hospitalName " +
            "FROM research r " +
            "JOIN hospital h ON r.hospital_id = h.id " +
            "WHERE r.id = #{researchId} AND r.hospital_id = #{hospitalId}")
    Research findByIdAndHospitalId(@Param("researchId") Long researchId, @Param("hospitalId") Long hospitalId);

    @Select("SELECT COUNT(rt.id) " +
            "FROM research_tester rt " +
            "JOIN tester t ON rt.tester_id = t.id " +
            "WHERE rt.research_id = #{researchId} AND t.hospital_id = #{workplaceId}")
    int countParticipants(@Param("researchId") Long researchId, @Param("workplaceId") Long workplaceId);

    @Select("SELECT COUNT(rt.id) " +
            "FROM research_tester rt " +
            "JOIN tester t ON rt.tester_id = t.id " +
            "WHERE rt.research_id = #{researchId} AND t.hospital_id = #{workplaceId} AND t.group_id IN (1, 2)")
    int countEnrollments(@Param("researchId") Long researchId, @Param("workplaceId") Long workplaceId);

    @Select("SELECT COUNT(rt.id) " +
            "FROM research_tester rt " +
            "JOIN tester t ON rt.tester_id = t.id " +
            "WHERE rt.research_id = #{researchId} AND t.hospital_id = #{workplaceId} AND t.crf = 'true'")
    int countSubmissions(@Param("researchId") Long researchId, @Param("workplaceId") Long workplaceId);

    @Select("SELECT t.* " +
            "FROM tester t " +
            "JOIN research_tester rt ON t.id = rt.tester_id " +
            "WHERE rt.research_id = #{researchId} AND t.hospital_id = #{hospitalId}")
    List<Tester> findParticipantsByResearchIdAndHospitalId(@Param("researchId") Long researchId, @Param("hospitalId") Long hospitalId);
}
